package com.dupf.common.util;

import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.net.URLEncoder;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * create by zhaotao on 2017/3/13 16:01.
 */
public class PoiUtils {
    private static final Logger log = LoggerFactory.getLogger(PoiUtils.class);

    /**
     * 下载文件时，针对不同浏览器，进行中文附件名的编码
     *
     * @param filename 下载文件名
     * @param request  请求
     * @return 编码后的下载附件名
     * @throws IOException
     */
    private static String encodeDownloadFilename(String filename, HttpServletRequest request) throws IOException {
        /*String agent = request.getHeader("user-agent");
        if (agent.contains("Firefox")) { // 火狐浏览器
            filename = "=?UTF-8?B?" + new BASE64Encoder().encode(filename.getBytes("utf-8")) + "?=";
            filename = filename.replaceAll("\r\n", "");
        } else { // IE及其他浏览器
            filename = URLEncoder.encode(filename, "utf-8");
            filename = filename.replace("+", " ");
        }*/
        return URLEncoder.encode(filename, "utf-8");
    }

    /**
     * 静态方法  解决创建Workbook 创建产生的问题
     * @param inp
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook createworkbook(InputStream inp) throws IOException,InvalidFormatException {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            return new HSSFWorkbook(inp);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }

    /**
     * 输出Excel文件
     *
     * @param wb       Excel文档对象对
     * @param fileName 文件名
     * @param request  HttpServletRequest
     * @param response HttpServletResponse
     */
    public static void sendExcel(Workbook wb, String fileName, HttpServletRequest request, HttpServletResponse response) {
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            response.reset();
            // 中文附件名编码
            fileName = PoiUtils.encodeDownloadFilename(fileName, request);
//            response.setContentType("multipart/form-data");
//            response.setContentType("application/octet-stream");
            response.setContentType("application/force-download");
//            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ";filename*=UTF-8''" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

            wb.write(outputStream);
            wb.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("PoiUtils exception", e);
        }
    }

    /**
     * 表的列头样式(居中)
     *
     * @param wb Excel文档对象
     * @return 表的列头单元格样式
     */
    public static HSSFCellStyle initColumnHeadStyle(HSSFWorkbook wb) {
        HSSFCellStyle columnHeadStyle = wb.createCellStyle();
        HSSFFont columnHeadFont = wb.createFont();
        columnHeadFont.setFontName("宋体");// 字体
        columnHeadFont.setFontHeightInPoints((short) 11);// 字号
        columnHeadFont.setBold(true);// 加粗
        columnHeadStyle.setFont(columnHeadFont);
        columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        columnHeadStyle.setLocked(true);// 样式锁定
        columnHeadStyle.setWrapText(true);// 文本应用样式
        //columnHeadStyle.setBorderBottom(BorderStyle.THICK); // 设置单元格的下边框为粗线
        columnHeadStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
        columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
        columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
        columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
        columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
        columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
        return columnHeadStyle;
    }

    /**
     * 非列头单元格样式(居中)
     *
     * @param wb Excel文档对象
     * @return
     */
    public static HSSFCellStyle initCellCenterStyle(HSSFWorkbook wb) {
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        HSSFCellStyle centerstyle = wb.createCellStyle();
        centerstyle.setFont(font);
        centerstyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        centerstyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        centerstyle.setWrapText(true);
        centerstyle.setBorderBottom(BorderStyle.THIN);// 下边框
        centerstyle.setBorderLeft(BorderStyle.THIN);// 左边框
        centerstyle.setBorderRight(BorderStyle.THIN);// 右边框
        centerstyle.setBorderTop(BorderStyle.THIN);// 上边框
        centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
        //centerstyle.setBorderLeft((short) 1);
        centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
        //centerstyle.setBorderRight((short) 1);
        //centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
        centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
        centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
        return centerstyle;
    }

    /**
     * 日期格式
     *
     * @param wb
     * @return
     */
    public static HSSFCellStyle dateCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle dateCellStyle = PoiUtils.initCellCenterStyle(wb);
        HSSFDataFormat format = wb.createDataFormat();
        dateCellStyle.setDataFormat(format.getFormat("yyyy/mm/dd"));
        return dateCellStyle;
    }

    /**
     * 时间格式
     *
     * @param wb
     * @return
     */
    public static HSSFCellStyle timeCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle timeCellStyle = PoiUtils.initCellCenterStyle(wb);
        HSSFDataFormat format = wb.createDataFormat();
        timeCellStyle.setDataFormat(format.getFormat("yyyy/mm/dd  hh:mm:ss"));
        return timeCellStyle;
    }

    /**
     * 检查表中是否提供系统所需要的列（字段）
     *
     * @param wb           工作簿对象
     * @param tableHeadMap 导入表格的表头配置（区分从各个总公司导出表的差异）
     *                     例如：{"trackingNumber":"运单号,运单号码,运单编号"}
     * @return 返回true为通过校验，否则不通过
     */
    public static boolean checkTableHeaderValidity(Workbook wb, Map tableHeadMap) {
        return checkTableHeaderValidity(wb, tableHeadMap, 0);
    }

    /**
     * 检查表中是否提供系统所需要的列（字段）
     *
     * @param wb           工作簿对象
     * @param tableHeadMap 导入表格的表头配置（区分从各个总公司导出表的差异）
     *                     例如：{"trackingNumber":"运单号,运单号码,运单编号"}
     * @return 返回true为通过校验，否则不通过
     */
    public static boolean checkTableHeaderValidity(Workbook wb, Map tableHeadMap, Integer headRowNum) {

        List<String[]> columnNamesList = new ArrayList<>();

        for (Object columnNamesStr : tableHeadMap.values()) {
            String[] columnNames = ((String) columnNamesStr).split(",");
            columnNamesList.add(columnNames);
        }

        for (Sheet sheet : wb) {
            Row headerRow = sheet.getRow(headRowNum);
            List<String> columnHeaderList = getColumnHeadList(headerRow);

            outer:
            for (String[] columnNames : columnNamesList) {
                for (String columnName : columnNames) {
                    // 如果要导入的Excel中包含所需列，将isLegal设为true，并继续外层循环。
                    if (columnHeaderList.contains(columnName)) {
                        continue outer;
                    }
                }
                return false;
            }
        }

        return true;
    }

    /**
     * 检查表中是否提供系统所需要的列（字段）
     *
     * @param wb           工作簿对象
     * @param tableHeadMap 导入表格的表头配置（区分从各个总公司导出表的差异）
     *                     例如：{"trackingNumber":"运单号,运单号码,运单编号"}
     * @return 返回true为通过校验，否则不通过
     */
    /*public static boolean checkTableHeaderValidity(Workbook wb, String module) {

        List<String[]> columnNamesList = new ArrayList<>();

        for (Object columnNamesStr : tableHeadMap.values()) {
            String[] columnNames = ((String) columnNamesStr).split(",");
            columnNamesList.add(columnNames);
        }

        for (Sheet sheet : wb) {
            Row headerRow = sheet.getRow(0);
            List<String> columnHeaderList = getColumnHeadList(headerRow);

            outer:
            for (String[] columnNames : columnNamesList) {
                for (String columnName : columnNames) {
                    // 如果要导入的Excel中包含所需列，将isLegal设为true，并继续外层循环。
                    if (columnHeaderList.contains(columnName)) {
                        continue outer;
                    }
                }
                return false;
            }
        }

        return true;
    }*/

    /**
     * 获取Excel的sheet页的列头（列名）的集合
     *
     * @param headerRow Row对象
     * @return List<String> 列名集合
     */
    public static List<String> getColumnHeadList(Row headerRow) {
        int columnNum = headerRow.getLastCellNum() - headerRow.getFirstCellNum();

        List<String> columnHeadList = new ArrayList<>();
        for (int j = 0; j < columnNum; j++) {
            String columnHeader = headerRow.getCell(j) != null ? headerRow.getCell(j).toString() : "";

            if (!StringUtils.isEmpty(columnHeader)) {
                columnHeadList.add(columnHeader);
            }
        }
        return columnHeadList;
    }

    /**
     * 通过对单元格遍历的形式来获取信息 ，这里要判断单元格的类型才可以取出值
     *
     * @param cell      单元格cell对象
     * @param evaluator 公式提取对象
     * @return 单元格的字符串类型的值
     */
    public static String getCellStringValue(Cell cell, FormulaEvaluator evaluator) {
        String value = "";
        if (cell == null) {
            return value;
        }

        try {
            switch (cell.getCellTypeEnum()) {
                case _NONE:
                case BLANK:
                case BOOLEAN:
                case ERROR:
                    value = cell.toString();
                    break;
                case FORMULA:
                    /*
                    // 会打印出原本单元格的公式
                    System.out.println(cell.getCellFormula());
                    NumberFormat nf = new DecimalFormat("#.#");
                    String value = nf.format(cell.getNumericCellValue());
                    */
                    CellValue cellValue = evaluator.evaluate(cell);
                    switch (cellValue.getCellTypeEnum()) {
                        case _NONE:
                        case BLANK:
                        case BOOLEAN:
                        case ERROR:
                            value = cell.toString();
                            break;
                        case NUMERIC:
                            value = String.valueOf(formatNumericCell(cellValue.getNumberValue(), cell));
                            break;
                        case STRING:
                            value = cell.getStringCellValue();
                            break;
                        default:
                            break;
                    }
                    break;
                case NUMERIC:
                    value = formatNumericCell(cell.getNumericCellValue(), cell);
                    break;
                case STRING:
                    value = cell.getStringCellValue();
                    break;
            }

        } catch (Exception e) {
            log.error("get cell's value exception", e);
        }

        return value;
    }

    /**
     * 通过对单元格遍历的形式来获取信息 ，这里要判断单元格的类型才可以取出值
     *
     * @param cell 单元格cell对象
     * @return 单元格的字符串类型的值
     */
    public static String getCellStringValue(Cell cell) {
        String value = "";
        if (cell == null) {
            return value;
        }

        try {
            switch (cell.getCellTypeEnum()) {
                case _NONE:
                case BLANK:
                case BOOLEAN:
                case ERROR:
                    value = cell.toString();
                    break;
                case NUMERIC:
                    value = formatNumericCell(cell.getNumericCellValue(), cell);
                    break;
                case STRING:
                    value = cell.getStringCellValue();
                    break;
            }

        } catch (Exception e) {
            log.error("get cell's value exception", e);
        }

        return value;
    }

    /**
     * 原样返回数值单元格的内容
     *
     * @param value 单元格数值
     * @param cell  单元格对象
     * @return 数值字符串
     */
    private static String formatNumericCell(Double value, Cell cell) {
        if (cell.getCellTypeEnum() != CellType.NUMERIC && cell.getCellTypeEnum() != CellType.FORMULA) {
            return null;
        }
        //isCellDateFormatted判断该单元格是"时间格式"或者该"单元格的公式算出来的是时间格式"
        if (DateUtil.isCellDateFormatted(cell)) {
            /*
            cell.getDateCellValue()碰到单元格是公式,会自动计算出Date结果
            Date date = DateUtil.getJavaDate(value);
            */
            Date date = cell.getDateCellValue();
            //DataFormatter dataFormatter = new DataFormatter();
            //Format format = dataFormatter.createFormat(cell);
            Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            return format.format(date);
        } else {
            /*
            String formatStr = cell.getCellStyle().getDataFormatString();
            if (formatStr.contains("0;")) {
                formatStr = "0";
            } else if (formatStr.contains("0.000")) {
                formatStr = "0.000";
            } else if (formatStr.contains("0.00")) {
                formatStr = "0.00";
            } else if (formatStr.contains("0.0")) {
                formatStr = "0.0";
            } else if (formatStr.equals("General")) {
                formatStr = "0";
            } else if (formatStr.contains("GENERAL")) {
                formatStr = "0";
            } else if (formatStr.contains("0_")) {
                formatStr = "0";
            } else if (formatStr.equals("0")) {
                formatStr = "0";
            }
            DecimalFormat df = new DecimalFormat(formatStr);
            return df.format(value);
            */
            DataFormatter dataFormatter = new DataFormatter();
            Format format = dataFormatter.createFormat(cell);
            return format.format(value);
        }
    }


    /**
     * 根据Excel的类别标识获取对应的javabean的名称
     *
     * @param excelIdentification Excel的标识
     * @return JavaBean名
     */
    public static String getModelNameByExcelIdentification(String excelIdentification) {
        String[] strings = excelIdentification.split("_");
        StringBuilder sb = new StringBuilder();
        for (String str : strings) {
            char[] cs = str.toCharArray();
            cs[0] -= 32;
            sb.append(String.valueOf(cs));
        }

        return sb.toString();
    }

    /**
     * 根据导入表的模块名获取配置文件中的表头与字段的映射
     *
     * @param module        Excel的标识
     * @param headerConfPre 表头配置名前缀
     * @return JavaBean名
     */
    public static Map<String, String> getImportHeaderConfigMap(String headerConfPre, String module) {

        String moduleStr = module.replace("_", ".");
        String tableHeadConfigName = headerConfPre + moduleStr.toLowerCase();
        String headers = PropertyManager.getRB("importexportconf", tableHeadConfigName);

        return (Map<String, String>) JSONObject.fromObject(headers);
    }

    /**
     * 获取列号和字段的对应关系
     *
     * @param tableHeadMap   系统预置的表头与字段的mapping
     * @param columnHeadList Excel表中读取的表头List集合
     */
    public static Map<Integer, String> getColumnFieldMap(Map<?, ?> tableHeadMap, List<String> columnHeadList) {
        // 存放字段的Map，key为列号，value为字段名
        Map<Integer, String> columnFieldMap = new HashMap<>();

        for (int i = 0; i < columnHeadList.size(); i++) {
            String columnHead = columnHeadList.get(i);

            for (Map.Entry<?, ?> entry : tableHeadMap.entrySet()) {
                String[] columnNames = String.valueOf(entry.getValue()).split(",");

                // 列可能有多个类似的名称
                for (String columnName : columnNames) {

                    if (columnHead != null && columnHead.equals(columnName)) {
                        columnFieldMap.put(i, String.valueOf(entry.getKey()));
                    }
                }
            }
        }

        return columnFieldMap;
    }

    /**
     * 初始化表头字段及样式
     *
     * @param wb    excel文本对象
     * @param names 表头字段名
     * @param sheet 文档sheet分页对象
     */
    public static void initTableHead(HSSFWorkbook wb, List<String> names, HSSFSheet sheet) {
        // 列头样式
        HSSFCellStyle columnHeadStyle = PoiUtils.initColumnHeadStyle(wb);
        // 初始化带边框的表头
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(20);
        HSSFCell cell = null;
        sheet.setColumnWidth(0, 2000);

        for (int i = 0; i < names.size(); i++) {
            cell = row.createCell(i);
            String name = names.get(i);
            cell.setCellValue(name);
            cell.setCellStyle(columnHeadStyle);

            sheet.setColumnWidth(i, 5000);
        }
    }

}
